
import 'dart:io';

import 'package:flutter/foundation.dart';
import 'package:sqflite/orm/table.dart';

import '../sqflite.dart';
import '_migration.dart';

///对数据库的简单封装
abstract class SqliteDataBase {

  ///数据库版本
  int get schemaVersion => 1;

  ///数据库表
  List<Table> get tables;

  Database? _database;
  Transaction? _transaction;
  bool _isOhos = false;
  bool _needTransferData = true;
  String? _path;

  Future _createDb() async {
    await close();
    _database = await openDatabase(_path!,
        version: schemaVersion,
        onUpgrade: (Database db, int oldVersion, int newVersion) => onUpgrade(db, oldVersion, newVersion));
  }

  Future<void> _checkDatabase() async {
    if(_isOhos && _database == null) await _createDb();
    assert(_database != null, 'must call SqliteDataBase#init(...)');
  }

  ///初始化数据库
  Future<void> init(String path) async {
    Sqflite.setDebugModeOn(kDebugMode);
    _path = path;
    _isOhos = Platform.operatingSystem == 'ohos';
    if(_database != null) return;
    await _createDb();
    await _createAll(_database!);
    if(_isOhos) {
      ///鸿蒙版的数据库，需要关闭之后，表和数据才能落地到db文件
      await close();
    }
  }

  ///关闭数据库
  Future<void> close() async {
    if(_database != null) {
      await _database!.close();
    }
    _database = null;
  }

  Future<void> _createAll(Database db) async {
    await db.transaction((txn) async {
      final migration = Migration();
      for(var table in tables) {
        await migration.createTable(txn, table);
      }
    });
  }

  ///框架支持表结构的自动升级，不需要重写
  Future<void> onUpgrade(Database db, int from, int to) async {
    await db.transaction((txn) async {
      final migration = Migration();
      await migration.queryTable(txn);
      for(var table in tables) {
        await migration.upgradeTable(txn, table);
      }
    });
  }

  Future<List<Map<String, Object?>>> query2(Type  tableClazz,
      {Map<String, dynamic>? condition,
        Map<String, dynamic>? noContain,
        Map<String, dynamic>? like,
        Map<String, dynamic>? noLike,
        Map<String, dynamic>? isValues,
        Map<String, dynamic>? isNotValues,
        Map<String, bool>? order, String? otherWhere, List<Object?>? otherArgs,
        bool? distinct,
        List<String>? columns,
        String? groupBy,
        String? having,
        int? limit,
        int? offset}) async {
    final table = getTableInfo(tableClazz);
    return query(table.tableName,
        condition: condition,
        noContain: noContain,
        limit: limit, isNotValues: isNotValues, isValues: isValues,
        like: like,
        noLike: noLike,
        order: order,
        columns: columns, distinct: distinct, groupBy: groupBy, having: having,
        otherWhere: otherWhere, offset: offset,
        otherArgs: otherArgs);
  }

  Future<List<Map<String, Object?>>> query(String table,
      {Map<String, dynamic>? condition,
      Map<String, dynamic>? noContain,
      Map<String, dynamic>? like,
      Map<String, dynamic>? noLike,
      Map<String, dynamic>? isValues,
      Map<String, dynamic>? isNotValues,
      Map<String, bool>? order, String? otherWhere, List<Object?>? otherArgs,
      bool? distinct,
      List<String>? columns,
      String? groupBy,
      String? having,
      int? limit,
      int? offset}) async {
    await _checkDatabase();
    final args = <Object?>[];
    final s = transferWhere(args,
        condition: condition, noContain: noContain, like: like, noLike: noLike,
        otherWhere: otherWhere, otherArgs: otherArgs, isNoValues: isNotValues, isValues: isValues);
    String? orderBy;
    if(order?.isNotEmpty == true) {
      var ob = '';
      var i = 0;
      order!.forEach((key, value) {
        if (i > 0)  ob += ',';
        if (value) {
          ob += ' $key ASC';
        } else {
          ob += ' $key DESC';
        }
        i++;
      });
      orderBy = ob;
    }
    _printLog('sql: $s $args');
    _needTransferData = false;
    return rawQuery(table,
        distinct: distinct,
        columns: columns,
        where: s,
        whereArgs: args,
        groupBy: groupBy,
        having: having,
        orderBy: orderBy,
        limit: limit,
        offset: offset);
  }

  Future<List<Map<String, Object?>>> rawQuery(String table,
      {bool? distinct,
      List<String>? columns,
      String? where,
      List<Object?>? whereArgs,
      String? groupBy,
      String? having,
      String? orderBy,
      int? limit,
      int? offset}) async {
    await _checkDatabase();
    whereArgs = _transferArgs(whereArgs);
    return await _db.query(table,
        distinct: distinct,
        columns: columns,
        where: where,
        whereArgs: whereArgs,
        groupBy: groupBy,
        having: having,
        orderBy: orderBy,
        limit: limit,
        offset: offset);
  }

  ///
  Future<int> delete2(Type tableClazz,Map<String, dynamic> condition, {Map<String, Object?>? wheres, Map<String, dynamic>? like}){
    final table = getTableInfo(tableClazz);
    return delete(table.tableName, wheres: condition, like: like);
  }
  ///
  Future<int> delete(String table, {Map<String, Object?>? wheres, Map<String, dynamic>? like}) async {
    await _checkDatabase();
    final args = <Object?>[];
    final s = transferWhere(args, condition: wheres, like: like);
    _needTransferData = false;
    return rawDelete(table, where: s, whereArgs: args);
  }
  ///
  Future<int> rawDelete(String table, {String? where, List<Object?>? whereArgs}) async {
    await _checkDatabase();
    whereArgs = _transferArgs(whereArgs);
    return _db.delete(table, where: where, whereArgs: whereArgs);
  }

  Future<int> rawCount(String table,
      {String? where, List<Object?>? whereArgs}) async {
    await _checkDatabase();
    var q = 'SELECT COUNT(1) FROM $table';
    if (where?.isNotEmpty == true) {
      q += ' WHERE $where';
    }
    whereArgs = _transferArgs(whereArgs);
    return _db.rawQuery(q, whereArgs).then((value) {
      return value.first.values.first as int;
    });
  }
  ///
  Future<int> count(String table,
      {Map<String, Object?>? wheres, Map<String, dynamic>? like}) async {
    await _checkDatabase();
    final args = <Object?>[];
    final s = transferWhere(args, condition: wheres, like: like);
    _needTransferData = false;
    return rawCount(table, where: s, whereArgs: args);
  }

  ///查询符合条件的总条数
  Future<int> count2(Type tableClazz,
      {Map<String, Object?>? wheres, Map<String, dynamic>? like})async{
    final table = getTableInfo(tableClazz);
    return count(table.tableName, wheres: wheres, like: like);
  }

  Future<int> insertTable(String table, Map<String, dynamic> values) async {
    await _checkDatabase();
    values = values.map((key, value) {
      return MapEntry(key, Table.transferBool(value));
    });
    final id = await _db.insert(table, values);
    if(_isOhos && _transaction == null) await close();
    return id;
  }

  Future<int> updateTable(String table, Map<String, Object?> data,{Map<String, Object?>? wheres, Map<String, dynamic>? like}) async {
    await _checkDatabase();
    final args = <Object?>[];
    final s = transferWhere(args, condition: wheres, like: like);
    _needTransferData = false;
    return rawUpdateTable(table, data, where: s, whereArgs: args);
  }
  ///更新数据
  Future<int> updateTable2(Type tableClazz, Map<String, dynamic> data,
      Map<String, dynamic> condition,
      {Map<String, dynamic>? like}) async {
    final table = getTableInfo(tableClazz);
    return updateTable(table.tableName, data, wheres: condition, like: like);
  }

  ///将查询条件转换成 sql查询语句
  String? transferWhere(
    List<Object?> args, {
    Map<String, dynamic>? condition,
    Map<String, dynamic>? noContain,
    Map<String, dynamic>? isValues,
    Map<String, dynamic>? isNoValues,
    Map<String, dynamic>? like,
    Map<String, dynamic>? noLike,
        String? otherWhere, List<Object?>? otherArgs
  }) {
    final where = StringBuffer();
    /// 包含
    condition?.removeWhere((key, value) => value == null || value is String && value.isEmpty);
    _appendWhere(where, args, condition, false);
    /// 不包含
    noContain?.removeWhere((key, value) => value == null || value is String && value.isEmpty);
    _appendWhere(where, args, noContain, true);
    /// 包含空值
    _appendWhere(where, args, isValues, false);
    /// 不包含空值
    _appendWhere(where, args, isNoValues, true);

    like?.removeWhere((key, value) => value == null || value is String && value.isEmpty);
    like?.forEach((key, value) {
      where.write(' AND $key LIKE ?');
      args.add(Table.transferBool(value));
    });
    noLike?.removeWhere((key, value) => value == null || value is String && value.isEmpty);
    noLike?.forEach((key, value) {
      where.write(' AND $key NOT LIKE ?');
      args.add(Table.transferBool(value));
    });
    if(otherWhere != null && otherWhere.isNotEmpty) {
      if(where.isNotEmpty) {
        where.write(' AND ');
      }
      where.write(otherWhere);
      if(otherArgs != null) {
        for (var element in otherArgs) {
          args.add(Table.transferBool(element));
        }
      }
    }
    if(where.isNotEmpty) return where.toString().substring(4);//去除第一个and
    return null;
  }

  /// 将查询条件转换成 sql查询语句
  void _appendWhere(StringBuffer where, List<Object?> args, Map<String, dynamic>? condition, bool not) {
    condition?.forEach((key, value) {
      if(value == null || value is String && value.isEmpty) {
        if(not) {
          where.write(' AND $key IS NOT NULL');
        } else {
          where.write(' AND $key IS NULL');
        }
      } else if(value is List) {
        if(value.isNotEmpty) {
          if(not) {
            where.write(' AND $key NOT IN (');
          } else {
            where.write(' AND $key IN (');
          }
          var j = 0;
          for (var v in value) {
            if(j > 0) where.write(',');
            where.write('?');
            args.add(Table.transferBool(v));
            j++;
          }
          where.write(')');
        }
      } else {
        if(not) {
          where.write(' AND $key <> ?');
        } else {
          where.write(' AND $key = ?');
        }
        args.add(Table.transferBool(value));
      }
    });
  }

  Future<int> rawUpdateTable(String table, Map<String, Object?> values,
      {String? where, List<Object?>? whereArgs}) async {
    await _checkDatabase();
    whereArgs = _transferArgs(whereArgs);
    final id = await _db.update(table, values,where: where, whereArgs: whereArgs);
    return id;
  }

  Future rawUpdate(String sql, [List<Object?>? args]) async {
    await _checkDatabase();
    return _db.rawUpdate(sql, args);
  }

  /// Calls in action must only be done using the transaction object
  /// using the database will trigger a dead-lock.
  ///
  /// ```
  /// await database.transaction((txn) async {
  ///   // Ok
  ///   await txn.execute('CREATE TABLE Test1 (id INTEGER PRIMARY KEY)');
  ///
  ///   // DON'T  use the database object in a transaction
  ///   // this will deadlock!
  ///   await database.execute('CREATE TABLE Test2 (id INTEGER PRIMARY KEY)');
  /// });
  Future<T>? transaction<T>(Future<T> Function(Transaction txn) action, {bool? exclusive}){
    return _database?.transaction((txn) async {
      _transaction = txn;
      final t = await action.call(txn);
      _transaction = null;
      return t;
    }, exclusive: exclusive);
  }

  DatabaseExecutor get _db {
    if(_transaction != null) {
      return _transaction!;
    }
    return _database!;
  }

  void _printLog(String msg) {
    if(kDebugMode) print(msg);
  }

  ///将不符合数据库的数据类型转换成数据库可识别的类型
  List<Object?> ? _transferArgs(List<Object?>? args) {
    if(_needTransferData) {
      return args?.map((e) => Table.transferBool(e)).toList();
    } else {
      _needTransferData = true;
      return args;
    }
  }

  Table getTableInfo(Type clazz) => throw '${clazz.toString()} is not a table';
}